﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class InvLotLocLpnDA : IInvLotLocLpnDA
    {
        private string ALLOCATED_QTY = "@ALLOCATED_QTY";
        private string ARCHIVE_DATE = "@ARCHIVE_DATE";
        private string ARCHIVE_QTY = "@ARCHIVE_QTY";
        private string CREATED_BY = "@CREATED_BY";
        private string EXPECTED_QTY = "@EXPECTED_QTY";
        private string LOC = "@LOC";
        private string LOT = "@LOT";
        private string LPN_ID = "@LPN_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PENDING_MOVE_IN = "@PENDING_MOVE_IN";
        private string PICKED_QTY = "@PICKED_QTY";
        private string PICKINPROCESS_QTY = "@PICKINPROCESS_QTY";
        private string QTY = "@QTY";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_INV_LOT_LOC_LPN_ID_UNIQUE = " SELECT COUNT(1) FROM @_@INV_LOT_LOC_LPN WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_DELETE_INV_LOT_LOC_LPN = " DELETE FROM @_@INV_LOT_LOC_LPN WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_INSERT_INV_LOT_LOC_LPN = " INSERT INTO @_@INV_LOT_LOC_LPN ( WH_ID, LOT, LOC, LPN_ID, OWNER_ID, SKU_ID, QTY, ALLOCATED_QTY, PICKED_QTY, EXPECTED_QTY, PICKINPROCESS_QTY, PENDING_MOVE_IN, ARCHIVE_QTY, ARCHIVE_DATE, STATUS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @LOT, @LOC, @LPN_ID, @OWNER_ID, @SKU_ID, @QTY, @ALLOCATED_QTY, @PICKED_QTY, @EXPECTED_QTY, @PICKINPROCESS_QTY, @PENDING_MOVE_IN, @ARCHIVE_QTY, @ARCHIVE_DATE, @STATUS, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT__INV_LOT_LOC_LPN_AND_LOT_ATTRIBUTE = "  SELECT DISTINCT ILLL.ROWID, ILLL.WH_ID, ILLL.LOT, ILLL.LOC, ILLL.LPN_ID, ILLL.OWNER_ID, ILLL.SKU_ID, ILLL.QTY, ILLL.ALLOCATED_QTY, ILLL.PICKED_QTY, ILLL.EXPECTED_QTY, ILLL.PICKINPROCESS_QTY, ILLL.PENDING_MOVE_IN, ILLL.ARCHIVE_QTY, ILLL.ARCHIVE_DATE, ILLL.STATUS, ILLL.TS, ILLL.CREATED_BY, ILLL.CREATED_DATE, ILLL.UPDATED_BY, ILLL.UPDATED_DATE  FROM  @_@INV_LOT_LOC_LPN  ILLL LEFT JOIN @_@LOT_ATTRIBUTE LA ON ILLL.LOT = LA.LOT";
        private string SQL_SELECT_ALL_INV_LOT_LOC_LPN = " SELECT  ROWID, WH_ID, LOT, LOC, LPN_ID, OWNER_ID, SKU_ID, QTY, ALLOCATED_QTY, PICKED_QTY, EXPECTED_QTY, PICKINPROCESS_QTY, PENDING_MOVE_IN, ARCHIVE_QTY, ARCHIVE_DATE, STATUS, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@INV_LOT_LOC_LPN ";
        private string SQL_SELECT_INV_LOT_LOC_LPN = "SELECT DISTINCT ILLL.ROWID, ILLL.WH_ID, ILLL.LOT, ILLL.LOC, ILLL.LPN_ID, ILLL.OWNER_ID, ILLL.SKU_ID, ILLL.QTY, ILLL.ALLOCATED_QTY, ILLL.PICKED_QTY, ILLL.EXPECTED_QTY, ILLL.PICKINPROCESS_QTY, ILLL.PENDING_MOVE_IN, ILLL.ARCHIVE_QTY, ILLL.ARCHIVE_DATE, ILLL.STATUS, ILLL.TS, ILLL.CREATED_BY, ILLL.CREATED_DATE, ILLL.UPDATED_BY, ILLL.UPDATED_DATE  FROM  @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOCATION L ON ILLL.LOC= L.LOC  ";
        private string SQL_SELECT_LOT_BY_LOC = " SELECT LOT FROM @_@INV_LOT_LOC_LPN WHERE LOC = @LOC ";
        private string SQL_SELECT_LOT_BY_LOC_LPN = " SELECT LOT FROM @_@INV_LOT_LOC_LPN WHERE LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_SELECT_LOT_BY_LPN = " SELECT LOT FROM @_@INV_LOT_LOC_LPN WHERE LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN = " UPDATE @_@INV_LOT_LOC_LPN SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, QTY = @QTY, ALLOCATED_QTY = @ALLOCATED_QTY, PICKED_QTY = @PICKED_QTY, EXPECTED_QTY = @EXPECTED_QTY, PICKINPROCESS_QTY = @PICKINPROCESS_QTY, PENDING_MOVE_IN = @PENDING_MOVE_IN, ARCHIVE_QTY = @ARCHIVE_QTY, ARCHIVE_DATE = @ARCHIVE_DATE, STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID  ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_ALLOCATED_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_FROM_LOC_ALLOCATED_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET ALLOCATED_QTY = ALLOCATED_QTY - @ALLOCATED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOC = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOC = @LOC ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOC_LPN = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT_LOC = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT_LOC_LPN = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT_LPN = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LPN = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_OWNER = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_SKU = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_PENDING_MOVE_IN_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET PENDING_MOVE_IN = @PENDING_MOVE_IN ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_PICK_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET PICKED_QTY = PICKED_QTY + @PICKED_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_PICKED_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET PICKED_QTY = PICKED_QTY + @PICKED_QTY , ALLOCATED_QTY = ALLOCATED_QTY - @PICKED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_PICKINPROCESS_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET PICKINPROCESS_QTY = @PICKINPROCESS_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET QTY = @QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_QTY_BY_CYCLE_COUNT = "UPDATE @_@INV_LOT_LOC_LPN SET QTY = @QTY,UPDATED_BY=@UPDATED_BY, UPDATED_DATE=GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_SHIPPED_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET QTY = QTY - @SHIPPED_QTY, PICKED_QTY = PICKED_QTY - @SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_STATUS = " UPDATE @_@INV_LOT_LOC_LPN SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_TO_LOC_PICKED_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET PICKED_QTY = PICKED_QTY + @PICKED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_UN_SHIPPED_QTY = " UPDATE @_@INV_LOT_LOC_LPN SET QTY = QTY + @SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string SQL_UPDATE_INV_LOT_LOC_LPN_UN_SHIPPED_QTY_TO_PICKED = " UPDATE @_@INV_LOT_LOC_LPN SET QTY = QTY + @SHIPPED_QTY, PICKED_QTY=PICKED_QTY+@SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckInvLotLocLpnIDUnique(string lot, string loc, string lpnID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@LOC", loc), new SqlParameter("@LPN_ID", lpnID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_INV_LOT_LOC_LPN_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckInvLotLocLpnIDUnique(DataBase dataBase, DbTransaction tran, string lot, string loc, string lpnID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@LOC", loc), new SqlParameter("@LPN_ID", lpnID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_INV_LOT_LOC_LPN_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int ExecuteInvLotLocLpnHold(DataBase dataBase, DbTransaction tran, string status, string holdType, InvHoldInfo invHoldInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (holdType.ToUpper())
                {
                    case "LOT":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LOT", invHoldInfo.Lot) };
                        break;

                    case "LOC":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOC.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LOC", invHoldInfo.Loc) };
                        break;

                    case "LPN":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LPN.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LPN_ID", invHoldInfo.LpnID) };
                        break;

                    case "LOT_LOC":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT_LOC.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LOT", invHoldInfo.Lot), new SqlParameter("@LOC", invHoldInfo.Loc) };
                        break;

                    case "LOT_LPN":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT_LPN.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LOT", invHoldInfo.Lot), new SqlParameter("@LPN_ID", invHoldInfo.LpnID) };
                        break;

                    case "LOC_LPN":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOC_LPN.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LOC", invHoldInfo.Loc), new SqlParameter("@LPN_ID", invHoldInfo.LpnID) };
                        break;

                    case "LOT_LOC_LPN":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_LOT_LOC_LPN.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@LOT", invHoldInfo.Lot), new SqlParameter("@LOC", invHoldInfo.Loc), new SqlParameter("@LPN_ID", invHoldInfo.LpnID) };
                        break;

                    case "OWNER":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_OWNER.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invHoldInfo.OwnerID) };
                        break;

                    case "SKU":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_HOLD_BY_SKU.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", invHoldInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invHoldInfo.OwnerID), new SqlParameter("@SKU_ID", invHoldInfo.SkuID) };
                        break;
                }
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public List<InvLotLocLpnInfo> GetInvLotLocLpnByCycleCountRule(DataBase dataBase, DbTransaction tran, string OwnerID, string SkuID, string orderBy, string whLoginID)
        {
            string sql = this.SQL_SELECT__INV_LOT_LOC_LPN_AND_LOT_ATTRIBUTE.Replace("@_@", whLoginID) + " WHERE ILLL.OWNER_ID=@OWNER_ID AND ILLL.SKU_ID=@SKU_ID" + orderBy;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_ID", this.SKU_ID) };
            List<InvLotLocLpnInfo> InvLotLocLpnInfoList = new List<InvLotLocLpnInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                InvLotLocLpnInfo invLotLocLpnInfo = new InvLotLocLpnInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                InvLotLocLpnInfoList.Add(invLotLocLpnInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return InvLotLocLpnInfoList;
        }

        public InvLotLocLpnInfo GetInvLotLocLpnByID(string lot, string loc, string lpnID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@LOC", loc), new SqlParameter("@LPN_ID", lpnID) };
            InvLotLocLpnInfo invLotLocLpnInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    invLotLocLpnInfo = new InvLotLocLpnInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return invLotLocLpnInfo;
        }

        public InvLotLocLpnInfo GetInvLotLocLpnByID(DataBase dataBase, DbTransaction tran, string lot, string loc, string lpnID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@LOC", loc), new SqlParameter("@LPN_ID", lpnID) };
            InvLotLocLpnInfo invLotLocLpnInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                invLotLocLpnInfo = new InvLotLocLpnInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invLotLocLpnInfo;
        }

        public List<InvLotLocLpnInfo> GetInvLotLocLpnByLoc(string Loc, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE  LOC = @LOC ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", Loc) };
            List<InvLotLocLpnInfo> InvLotLocLpnInfoList = new List<InvLotLocLpnInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    InvLotLocLpnInfo invLotLocLpnInfo = new InvLotLocLpnInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    InvLotLocLpnInfoList.Add(invLotLocLpnInfo);
                }
            }
            return InvLotLocLpnInfoList;
        }

        public List<InvLotLocLpnInfo> GetInvLotLocLpnByLocType(string OwnerID, string SkuID, string Loc, string LocType, string whLoginID)
        {
            string sql = this.SQL_SELECT_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE ILLL.OWNER_ID=@OWNER_ID AND ILLL.SKU_ID=@SKU_ID AND L.LOC_USAGE =@LOC_USAGE AND ILLL.LOC != @LOC ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", Loc), new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_ID", SkuID), new SqlParameter("@LOC_USAGE", LocType) };
            List<InvLotLocLpnInfo> InvLotLocLpnInfoList = new List<InvLotLocLpnInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    InvLotLocLpnInfo invLotLocLpnInfo = new InvLotLocLpnInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    InvLotLocLpnInfoList.Add(invLotLocLpnInfo);
                }
            }
            return InvLotLocLpnInfoList;
        }

        public List<InvLotLocLpnInfo> GetInvLotLocLpnByLotAttribute(ReceiptLotInfo lotAttribute, string ownerID, string Skuid, string whLoginID)
        {
            string sql = " SELECT  A.ROWID, A.WH_ID, A.LOT, A.LOC, A.LPN_ID, A.OWNER_ID, A.SKU_ID, A.QTY, A.ALLOCATED_QTY, A.PICKED_QTY, A.EXPECTED_QTY, A.PICKINPROCESS_QTY, A.PENDING_MOVE_IN, A.ARCHIVE_QTY, A.ARCHIVE_DATE, A.STATUS, A.TS, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE  FROM  @_@INV_LOT_LOC_LPN A  INNER JOIN  @_@LOT_ATTRIBUTE LA ON  A.LOT=LA.LOT  WHERE  A.OWNER_ID =@OWNER_ID AND A.SKU_ID = @SKU_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.OWNER_ID, ownerID), new SqlParameter(this.SKU_ID, Skuid) };
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr01))
            {
                sql = sql + " AND LA.LOT_ATTR01 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr01);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr02))
            {
                sql = sql + " AND LA.LOT_ATTR02 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr02);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr03))
            {
                sql = sql + " AND LA.LOT_ATTR03 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr03);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr04))
            {
                sql = sql + " AND LA.LOT_ATTR04 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr04);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr05))
            {
                sql = sql + " AND LA.LOT_ATTR05 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr05);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr06))
            {
                sql = sql + " AND LA.LOT_ATTR06 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr06);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr07))
            {
                sql = sql + " AND LA.LOT_ATTR07 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr07);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr08))
            {
                sql = sql + " AND LA.LOT_ATTR08 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr08);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr09))
            {
                sql = sql + " AND LA.LOT_ATTR09 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr09);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr10))
            {
                sql = sql + " AND LA.LOT_ATTR10 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (lotAttribute.ProduceDate != CS00003)
            {
                CS00003 = lotAttribute.ProduceDate;
                sql = sql + " AND LA.PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (lotAttribute.ExpiryDate != CS00003)
            {
                CS00003 = lotAttribute.ExpiryDate;
                sql = sql + " AND LA.EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(lotAttribute.ExternalLot))
            {
                sql = sql + " AND LA.EXTERNAL_LOT = " + StrUtils.FormatSQLStr(lotAttribute.ExternalLot);
            }
            if (!string.IsNullOrEmpty(lotAttribute.VendorID))
            {
                sql = sql + " AND LA.VENDOR_ID = " + StrUtils.FormatSQLStr(lotAttribute.VendorID);
            }
            CS00003 = new DateTime();
            if (lotAttribute.ReceiptDate != CS00003)
            {
                CS00003 = lotAttribute.ReceiptDate;
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),LA.RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(CS00003.ToShortDateString()) + ",23) AS DATETIME)";
            }
            sql = (sql + " ORDER BY LA.RECEIPT_DATE,A.LOC,A.QTY ASC").Replace("@_@", whLoginID);
            List<InvLotLocLpnInfo> InvLotLocLpnInfoList = new List<InvLotLocLpnInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    InvLotLocLpnInfo invLotLocLpnInfo = new InvLotLocLpnInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()));
                    InvLotLocLpnInfoList.Add(invLotLocLpnInfo);
                }
            }
            return InvLotLocLpnInfoList;
        }

        public DataSet GetInvLotLocLpnByQueryList(List<string> sqlWhere, InvLotLocLpnQueryEntity invlotloclpnQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = " SELECT DISTINCT A.ROWID AS ROWID, A.WH_ID AS WH_ID,A.LOT AS LOT,A.LOC AS LOC,A.LPN_ID AS LPN_ID,A.OWNER_ID AS OWNER_ID,A.SKU_ID AS SKU_ID,A.QTY AS QTY,A.QTY-A.ALLOCATED_QTY-A.PICKED_QTY-A.EXPECTED_QTY- A.PICKINPROCESS_QTY AS AVAILABLE_QTY, A.ALLOCATED_QTY AS ALLOCATED_QTY,A.PICKED_QTY AS PICKED_QTY,A.EXPECTED_QTY AS EXPECTED_QTY,A.PICKINPROCESS_QTY AS PICKINPROCESS_QTY,A.PENDING_MOVE_IN AS PENDING_MOVE_IN,A.ARCHIVE_QTY AS ARCHIVE_QTY,A.ARCHIVE_DATE AS ARCHIVE_DATE,A.STATUS AS HOLD_STATUS,LAC.DESCR AS SKU_PROPERTY, A.TS,A.CREATED_BY,A.CREATED_DATE,A.UPDATED_BY,A.UPDATED_DATE,B.DESCR,B.PACK_ID, L.LOT_ATTR01 AS LOT_ATTR01,L.LOT_ATTR02 AS LOT_ATTR02,L.LOT_ATTR03 AS LOT_ATTR03,L.LOT_ATTR04 AS LOT_ATTR04,L.LOT_ATTR05 AS LOT_ATTR05,L.LOT_ATTR06 AS LOT_ATTR06,L.LOT_ATTR07 AS LOT_ATTR07,L.LOT_ATTR08 AS LOT_ATTR08,L.LOT_ATTR09 AS LOT_ATTR09,L.LOT_ATTR10 AS LOT_ATTR10, L.PRODUCE_DATE AS PRODUCE_DATE,L.EXPIRY_DATE AS EXPIRY_DATE,L.RECEIPT_DATE AS RECEIPT_DATE, L.EXTERNAL_LOT AS EXTERNAL_LOT,L.VENDOR_ID AS VENDOR_ID,V.COMPANY AS VENDOR_NAME,R.EXTERNAL_RECEIPT_ID,C.PACK_ID,C.PACK_UOM1 AS UOM_ID  FROM  @_@INV_LOT_LOC_LPN A  LEFT JOIN @_@SKU B ON A.SKU_ID = B.SKU_ID AND A.OWNER_ID = B.OWNER_ID  LEFT JOIN @_@PACK C ON B.PACK_ID= C.PACK_ID LEFT JOIN @_@LOT_ATTRIBUTE L ON A.LOT = L.LOT  LEFT JOIN @_@VENDOR V ON V.VENDOR_ID = L.VENDOR_ID  LEFT JOIN @_@RECEIPT R ON L.LOT_ATTR10 = R.EXTERNAL_RECEIPT_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON l.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' INNER JOIN SYSADM.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID";
            temp = temp.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!invlotloclpnQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, invlotloclpnQuery.CurrentPage, invlotloclpnQuery.PageSize, invlotloclpnQuery.SortField, invlotloclpnQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                string CS00002 = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
                temp = CS00002 + " ORDER BY " + invlotloclpnQuery.SortField + " " + invlotloclpnQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public List<string> GetLotByLocLpn(DataBase dataBase, DbTransaction tran, string getType, string loc, string lpn, string whLoginID)
        {
            string sql = string.Empty;
            SqlParameter[] paras = null;
            if (getType.ToUpper() == "LOC")
            {
                sql = this.SQL_SELECT_LOT_BY_LOC.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@LOC", loc) };
            }
            else if (getType.ToUpper() == "LPN")
            {
                sql = this.SQL_SELECT_LOT_BY_LPN.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@LPN", lpn) };
            }
            else if (getType.ToUpper() == "LOC_LPN")
            {
                sql = this.SQL_SELECT_LOT_BY_LOC_LPN.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@LOC", loc), new SqlParameter("@LPN", lpn) };
            }
            List<string> lots = new List<string>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                if (!lots.Contains(reader[0].ToString()))
                {
                    lots.Add(reader[0].ToString());
                }
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return lots;
        }

        public int InsertInvLotLocLpn(InvLotLocLpnInfo invLotLocLpnInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotLocLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLotLocLpn_Parameters(invLotLocLpnInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_INSERT_INV_LOT_LOC_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertInvLotLocLpn(DataBase dataBase, DbTransaction tran, InvLotLocLpnInfo invLotLocLpnInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotLocLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLotLocLpn_Parameters(invLotLocLpnInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INV_LOT_LOC_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_InvLotLocLpn_Parameters(InvLotLocLpnInfo invLotLocLpnInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.LOC, SqlDbType.VarChar, 20), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PICKED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.EXPECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PICKINPROCESS_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PENDING_MOVE_IN, SqlDbType.Decimal, 13), new SqlParameter(this.ARCHIVE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ARCHIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), 
                new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(invLotLocLpnInfo.WhID))
            {
                paramArray[0].Value = invLotLocLpnInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = invLotLocLpnInfo.Lot;
            paramArray[2].Value = invLotLocLpnInfo.Loc;
            paramArray[3].Value = invLotLocLpnInfo.LpnID;
            paramArray[4].Value = invLotLocLpnInfo.OwnerID;
            paramArray[5].Value = invLotLocLpnInfo.SkuID;
            paramArray[6].Value = invLotLocLpnInfo.Qty;
            paramArray[7].Value = invLotLocLpnInfo.AllocatedQty;
            paramArray[8].Value = invLotLocLpnInfo.PickedQty;
            paramArray[9].Value = invLotLocLpnInfo.ExpectedQty;
            paramArray[10].Value = invLotLocLpnInfo.PickinprocessQty;
            paramArray[11].Value = invLotLocLpnInfo.PendingMoveIn;
            paramArray[12].Value = invLotLocLpnInfo.ArchiveQty;
            paramArray[13].Value = invLotLocLpnInfo.ArchiveDate;
            if (!string.IsNullOrEmpty(invLotLocLpnInfo.Status))
            {
                paramArray[14].Value = invLotLocLpnInfo.Status;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = invLotLocLpnInfo.CreatedBy;
            paramArray[0x10].Value = invLotLocLpnInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateInvLotLocLpn(InvLotLocLpnInfo invLotLocLpnInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotLocLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLotLocLpn_Parameters(invLotLocLpnInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPDATE_INV_LOT_LOC_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvLotLocLpn(DataBase dataBase, DbTransaction tran, InvLotLocLpnInfo invLotLocLpnInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invLotLocLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvLotLocLpn_Parameters(invLotLocLpnInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_LOT_LOC_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvLotLocLpn(DataBase dataBase, DbTransaction tran, InvLotLocLpnInfo invLotLocLpnInfo, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (updateType)
                {
                    case "Qty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@QTY", invLotLocLpnInfo.Qty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "Status":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_STATUS.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", invLotLocLpnInfo.Status), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "AllocatedQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_ALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invLotLocLpnInfo.AllocatedQty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "FromLocAllocatedQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_FROM_LOC_ALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invLotLocLpnInfo.AllocatedQty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "PickedQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invLotLocLpnInfo.PickedQty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "ToLocPickedQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_TO_LOC_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invLotLocLpnInfo.PickedQty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "ShippedQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotLocLpnInfo.Qty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "PickInProcessQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_PICKINPROCESS_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKINPROCESS_QTY", invLotLocLpnInfo.PickinprocessQty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "PendingMoveIn":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_PENDING_MOVE_IN_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PENDING_MOVE_IN", invLotLocLpnInfo.PendingMoveIn), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "PickedQtyOnly":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_PICK_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invLotLocLpnInfo.PickedQty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "UnShippedQty":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_UN_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotLocLpnInfo.Qty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;

                    case "UnShippedQtyToPicked":
                        sql = this.SQL_UPDATE_INV_LOT_LOC_LPN_UN_SHIPPED_QTY_TO_PICKED.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invLotLocLpnInfo.Qty), new SqlParameter("@UPDATED_BY", invLotLocLpnInfo.UpdatedBy), new SqlParameter("@LOT", invLotLocLpnInfo.Lot), new SqlParameter("@LOC", invLotLocLpnInfo.Loc), new SqlParameter("@LPN_ID", invLotLocLpnInfo.LpnID) };
                        break;
                }
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvLotLocLpnQty(DataBase dataBase, DbTransaction tran, decimal qty, string Loc, string Lot, string LpnID, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@QTY", qty), new SqlParameter("@LOC", Loc), new SqlParameter("@Lot", Lot), new SqlParameter("@LPN_ID", LpnID), new SqlParameter("@UPDATED_BY", updateBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_LOT_LOC_LPN_QTY_BY_CYCLE_COUNT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

